# import pandas as pd
# import matplotlib.pyplot as plt
#
# plt.rcParams["font.family"] = ["SimHei", "WenQuanYi Micro Hei", "Heiti TC"]
# plt.rcParams["axes.unicode_minus"] = False
# # 读取数据
# df_sales = pd.read_excel("餐饮连锁品牌数据.xlsx", sheet_name="销售记录")
# df_dishes = pd.read_excel("餐饮连锁品牌数据.xlsx", sheet_name="菜品信息")
#
# # 提取核心菜品ID
# core_dish_ids = set(df_dishes[df_dishes["是否核心菜品"] == "是"]["菜品ID"])
#
# # 标记每条销售记录是否为核心菜品
# df_sales["是否核心菜品"] = df_sales["菜品ID"].isin(core_dish_ids)
#
# # 按时段分组统计
# df_period = df_sales.groupby("时段").agg(
#     客流量=("销售ID", "count"),
#     核心菜品销量=("是否核心菜品", "sum")
# ).reset_index()
#
# # 设置时段顺序
# period_order = ["早餐", "午餐", "下午茶", "晚餐"]
# df_period["时段"] = pd.Categorical(df_period["时段"], categories=period_order, ordered=True)
# df_period = df_period.sort_values("时段")
#
# # 绘制折线图
# fig, ax1 = plt.subplots(figsize=(10, 6))
#
# # 左轴：客流量
# ax1.plot(df_period["时段"], df_period["客流量"], marker='o', label="客流量", color='blue', linewidth=2)
# ax1.set_ylabel("客流量（订单数）", color='blue')
# ax1.tick_params(axis='y', labelcolor='blue')
# ax1.set_ylim(0, df_period["客流量"].max() * 1.1)
#
# # 右轴：核心菜品销量
# ax2 = ax1.twinx()
# ax2.plot(df_period["时段"], df_period["核心菜品销量"], marker='s', label="核心菜品销量", color='red', linewidth=2)
# ax2.set_ylabel("核心菜品销量（份）", color='red')
# ax2.tick_params(axis='y', labelcolor='red')
# ax2.set_ylim(0, df_period["核心菜品销量"].max() * 1.1)
#
# # 标题和图例
# plt.title("各时段客流量与核心菜品销量趋势")
# fig.tight_layout()
# fig.legend(loc="upper left", bbox_to_anchor=(0.1, 0.9))
#
# plt.show()

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 读取Excel文件
file_path = '餐饮连锁品牌数据.xlsx'  # 请确保文件路径正确

# 读取三个sheet
store_df = pd.read_excel(file_path, sheet_name='门店信息')
dishes_df = pd.read_excel(file_path, sheet_name='菜品信息')
sales_df = pd.read_excel(file_path, sheet_name='销售记录')

print("数据读取完成！")
print(f"销售记录总数: {len(sales_df)}")

# 数据清洗
# 1. 过滤掉无效的销售记录（缺失菜品ID、销售数量<=0、销售单价<=0的记录）
clean_sales = sales_df[
    sales_df['菜品ID'].notna() &
    sales_df['销售数量'].notna() &
    sales_df['销售单价(元)'].notna() &
    (sales_df['销售数量'] > 0) &
    (sales_df['销售单价(元)'] > 0)
    ].copy()

print(f"清洗后有效销售记录: {len(clean_sales)}")

# 2. 合并销售数据和菜品信息
merged_data = clean_sales.merge(dishes_df, on='菜品ID', how='inner')

# 3. 计算每笔销售的金额
merged_data['销售金额'] = merged_data['销售数量'] * merged_data['销售单价(元)']

print(f"合并后有效记录: {len(merged_data)}")

# 按菜品类别统计销售总额
category_sales = merged_data.groupby('菜品类别')['销售金额'].sum().sort_values(ascending=False)

# 计算占比
category_percentage = (category_sales / category_sales.sum() * 100).round(1)

print("\n各菜品类别销售占比:")
for category, percentage in category_percentage.items():
    print(f"{category}: {percentage}%")

# 绘制饼图
plt.figure(figsize=(12, 8))

# 定义颜色
colors = plt.cm.Set3(np.linspace(0, 1, len(category_percentage)))

# 绘制饼图
wedges, texts, autotexts = plt.pie(
    category_percentage.values,
    labels=category_percentage.index,
    autopct='%1.1f%%',
    startangle=90,
    colors=colors,
    textprops={'fontsize': 12}
)

# 美化百分比文字
for autotext in autotexts:
    autotext.set_color('white')
    autotext.set_fontweight('bold')
    autotext.set_fontsize(10)

# 设置标题
plt.title('餐饮连锁品牌各菜品类别销售总额占比', fontsize=16, fontweight='bold', pad=20)

# 添加图例
plt.legend(
    wedges,
    [f'{label}: {percent}%' for label, percent in zip(category_percentage.index, category_percentage.values)],
    title="菜品类别",
    loc="center left",
    bbox_to_anchor=(1, 0, 0.5, 1),
    fontsize=10
)

plt.tight_layout()
plt.show()

# 输出分析建议
print("\n" + "=" * 50)
print("分析与建议:")
print("=" * 50)

for category, percentage in category_percentage.items():
    if percentage >= 15:
        status = "核心品类"
        suggestion = "应重点维护和创新，保证品质"
    elif percentage >= 8:
        status = "重要品类"
        suggestion = "有发展潜力，可适当投入资源"
    elif percentage >= 5:
        status = "常规品类"
        suggestion = "保持现状，观察表现"
    else:
        status = "预警品类"
        suggestion = "需分析原因，考虑优化或淘汰"

    print(f"{category}({percentage}%): {status} - {suggestion}")

# 保存结果到Excel
output_data = []
for category, percentage in category_percentage.items():
    output_data.append({
        '菜品类别': category,
        '销售占比(%)': percentage,
        '销售总额(元)': category_sales[category]
    })

output_df = pd.DataFrame(output_data)
output_df.to_excel('菜品类别销售分析.xlsx', index=False)
print(f"\n分析结果已保存到 '菜品类别销售分析.xlsx'")